package contacts.dao.impl;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import contacts.po.Room;
import contacts.po.Student;
import contacts.db.DbConnection;
import contacts.dao.IStudentDao;

public class StudentDaoImpl implements IStudentDao{	
	/**
	 *
	 *新增联系人
	 *
	 */
	@Override
	public void save(Student student) {
		Connection conn = DbConnection.getConnection();
		int studentId = student.getStudentId();
		String studentNo = student.getStudentNo();
		String studentName = student.getStudentName();
		Room room = student.getRoom();
		int roomNo = room.getRoomNo();
		Date birthday = student.getBirthday();
		String telephone = student.getTelephone();
		String email = student.getEmail();
		String birthPlace = student.getBirthPlace();
		String address = student.getAddress();
		String sql = "INSERT INTO student VALUES(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps  = conn.prepareStatement(sql);
			ps.setInt(1,studentId);
			ps.setString(2,studentNo);
			ps.setString(3,studentName);
			ps.setDate(4, (java.sql.Date) birthday);
			ps.setString(5,telephone);
			ps.setString(6,email);
			ps.setString(7,birthPlace);
			ps.setString(8,address);
			ps.setInt(9,roomNo);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbConnection.close(null, ps);
		}	
	}

	/**
	 *
	 *更新联系人信息
	 *
	 */
	@Override
	public void update(Student student) {
		Connection conn = DbConnection.getConnection();
		String studentNo = student.getStudentNo();
		String studentName = student.getStudentName();
		Room room = student.getRoom();
		int  roomNo = room.getRoomNo();
		Date birthday = student.getBirthday();
		String telephone = student.getTelephone();
		String email = student.getEmail();
		String birthPlace = student.getBirthPlace();
		String address = student.getAddress();
		int studentId = student.getStudentId();
		String roomName = room.getRoomName();
		String sql = "UPDATE student,room SET student_no=?,student_name=?,student_birthday=?,student_telephone=?" + 
			          ",student_email=?,student_birthplace=?,student_address=?,student_room_no=?,room_name=? WHERE student.student_room_no=room.room_no AND student_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1,studentNo);
			ps.setString(2,studentName);
			ps.setDate(3,(java.sql.Date)birthday);
			ps.setString(4,telephone);
			ps.setString(5,email);
			ps.setString(6,birthPlace);
			ps.setString(7,address);
			ps.setInt(8,roomNo);
			ps.setString(9,roomName);
			ps.setInt(10,studentId);
			ps.executeUpdate();
			 ps.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
		
	}

	/**
	 *
	 *删除联系人信息
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM student WHERE student_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
		
	}
	
	/**
	 *根据编号查询联系人的信息
	 *student_id为主键
	 *
	 */
	@Override
	public Student get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Student> studentList = new ArrayList<Student>();
		String sql = "SELECT student_id,student_no,student_name,student_birthday,student_telephone" + 
				",student_email,student_birthplace,student_address,student_room_no,room_name FROM student  AS s,room AS r WHERE s.student_room_no=r.room_no AND student_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs = ps.executeQuery();
				while (rs.next()){
					int studentId = rs.getInt(1);
					String studentNo = rs.getString(2);
					String studentName = rs.getString(3);
					Date birthday = rs.getDate(4);
					String telephone = rs.getString(5);
					String email = rs.getString(6);
					String birthPlace = rs.getString(7);
					String address = rs.getString(8);
					int roomNo = rs.getInt(9);
					String roomName = rs.getString(10);
					Room room = new Room(roomNo,roomName);
					Student student= new Student(studentId,studentNo,studentName, birthday,
							telephone,email,birthPlace,address,room);
					studentList.add(student);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(rs, ps);
			}
			return studentList.get(0);
		}

		/**
		 *
		 * 查询所有联系人的信息
		 */
		@Override
		public List<Student> findAll() {
			Connection conn = DbConnection.getConnection();
			List<Student> studentList = new ArrayList<Student>();
			String sql = "SELECT student_id,student_no,student_name,student_birthday,student_telephone"
					+ ",student_email,student_birthplace,student_address,student_room_no,room_name FROM student  AS s,room AS r WHERE s.student_room_no=r.room_no";
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					int studentId = rs.getInt(1);
					String studentNo = rs.getString(2);
					String studentName = rs.getString(3);
					Date birthday = rs.getDate(4);
					String telephone = rs.getString(5);
					String email = rs.getString(6);
					String birthPlace = rs.getString(7);
					String address = rs.getString(8);
					int roomNo = rs.getInt(9);
					String roomName = rs.getString(10);
					Room room = new Room(roomNo, roomName);
					Student student = new Student(studentId, studentNo, studentName, birthday, telephone, email,
							birthPlace, address, room);
					studentList.add(student);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				DbConnection.close(rs, ps);
			}
			return studentList;
		}
	}
    
